﻿using System;
using System.Collections.Generic;
using System.Data;
// included for developing
using System.Diagnostics;
using System.Globalization;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace PoliceHealthImportSystem.Class
{    

    public class ExcelReader : IDisposable
    {          
        #region Methods
 
        public DataTable GetDataTableFromExcel(string excelFile,int firstDataRow)
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range usedRange;
          
            int rowCounter = 0;
            int columnCounter = 0;
            int totalRows = 0;
            int totalColumns = 0;                   

            DataTable excelDatatable = new DataTable();
            List<string> columnNames = new List<string>();
    
            xlApp = new Excel.Application();

            try
            {
                xlWorkBook = xlApp.Workbooks.Open(excelFile, 0, true, 5, ""
                                                , "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false
                                                , false, 0, true, 1, 0);
                //Get first worksheet
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                //Convert to Excel.Range object
                usedRange = xlWorkSheet.UsedRange;
                               
                totalRows = usedRange.Rows.Count;
                totalColumns = usedRange.Columns.Count;
        
                while (excelDatatable.Columns.Count < totalColumns)   
                {
                    excelDatatable.Columns.Add();                        
                }
                
                for (rowCounter = firstDataRow; rowCounter <= totalRows; rowCounter++)
                {
                    // Prepare array for data row
                    object[] array = new object[totalColumns];

                    for (columnCounter = 0; columnCounter < totalColumns; columnCounter++)
                    {
                        array[columnCounter] = (usedRange.Cells[rowCounter, columnCounter + 1] as Excel.Range).Value2;
                    }
                                       
                    // Add row to data table
                    excelDatatable.Rows.Add(array);   
                }

                //Close excel file without save
                xlWorkBook.Close(false, Type.Missing, Type.Missing);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);               
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error" + ex.ToString());
            }

            return excelDatatable;
        }

        public bool IsCorrectTemplate(DataTable data)
        {
            bool isCorrectTemplate = false;
            if(data != null){
                isCorrectTemplate = (data.Columns.Count == 89);
            }
            return isCorrectTemplate;
        }
   
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception exception)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + exception.ToString());
                throw exception;
            }
            finally
            {
                GC.Collect();
            }
        }

        public void Dispose()
        {
            GC.Collect();
        }

        public bool ExportDataTableToExcel(DataTable dt, string filepath)
        {

            Excel.Application oXL;
            Excel.Workbook oWB;
            Excel.Worksheet oSheet;
            Excel.Range oRange;

            try
            {
                // Start Excel and get Application object. 
                oXL = new Excel.Application();

                // Set some properties 
                oXL.Visible = true;
                oXL.DisplayAlerts = false;

                // Get a new workbook. 
                oWB = oXL.Workbooks.Add(Missing.Value);

                // Get the Active sheet 
                oSheet = (Excel.Worksheet)oWB.ActiveSheet;
                oSheet.Name = "เอกสาร1";

                int rowCount = 1;
                foreach (DataRow dr in dt.Rows)
                {
                    rowCount += 1;
                    for (int i = 1; i < dt.Columns.Count + 1; i++)
                    {
                        // Add the header the first time through 
                        if (rowCount == 2)
                        {
                            oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                        }
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }

                // Resize the columns 
                oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                              oSheet.Cells[rowCount, dt.Columns.Count]);
                oRange.EntireColumn.AutoFit();
           
                // Save the sheet and close 
                oSheet = null;
                oRange = null;
                oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlExclusive,
                    Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value);
                oWB.Close(Missing.Value, Missing.Value, Missing.Value);
                oWB = null;
                oXL.Quit();
            }
            catch
            {
                throw;
            }
            finally
            {
                // Clean up 
                // NOTE: When in release mode, this does the trick 
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }

            return true;
        }

        #endregion Methods
    }
}
